<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Cafebz_model extends CI_Model
{
    private $db_sf;
    private $db_cox;
    private $db_velpa;
    
    public function __construct()
    {
        parent::__construct();
        $this->load->config('cafebz',TRUE);
    }
    
    public function load_sf_db()
    {
        $config = $this->config->item('sf','cafebz');
        $this->db_sf = $this->load->database($config, TRUE);
    }
    
    public function load_cox_db()
    {
        $config = $this->config->item('cox','cafebz');
        $this->db_cox = $this->load->database($config, TRUE);
    }
    
    public function load_velpa_db()
    {
        $config = $this->config->item('velpa','cafebz');
        $this->db_velpa = $this->load->database($config, TRUE);
    }
    
    public function insert_sf_sales($data)
    {
        $this->load->database();
        $this->db->insert('sf_sales', $data);
        $this->db->close();
    }
    
    public function get_sf_sales($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('sf_sales',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_sf_sales($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('sf_sales', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function insert_sf_payment($data)
    {
        $this->load->database();
        $this->db->insert('sf_payment', $data);
        $this->db->close();
    }
    
    public function get_sf_payment($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('sf_payment',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_sf_payment($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('sf_payment', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function get_sf_revenue($time)
    {
        $this->load_sf_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT ROUND(SUM([subtotal]),0) AS subtotal, ROUND(SUM([discount]),0) AS discount, ROUND(SUM([serviceAmount]),0) AS \"service\", 
                ROUND(SUM([taxAmount]),0) AS tax, ROUND(SUM([roundingAmount]),0) AS rounding FROM [TBL_SALES] 
                WHERE [closed] <= '$tomorrow 07:00' AND [closed] >= '$today 16:00'";
        $query = $this->db_sf->query($sql);
        $result = $query->row_array();
        
        // Get officer check discount
        $sql = "SELECT SUM(AMOUNT) AS amount from [TBL_SALES_LINES], [TBL_SALES] where [TBL_SALES].[id]=[TBL_SALES_LINES].[sales_id] AND [discount] > 0
                AND ([TBL_SALES_LINES].[description] = 'Officher Chek' OR [TBL_SALES_LINES].[description] = 'Disc Nominal') 
                AND [closed] <= '$tomorrow 07:00' AND [closed] >= '$today 16:00'";
        $query = $this->db_sf->query($sql);
        $rslt = $query->row_array();
        $oc = (is_null($rslt['amount']))? 0 : $rslt['amount'];
        $result['discount_customer'] = $result['discount'] - $oc;
        $result['discount_ocheck'] = $oc;
        
        $query->free_result();
        $this->db_sf->close();
        
        return $result;
    }
    
    public function get_sf_media_collection($time)
    {
        $this->load_sf_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT COUNT([TBL_SALES_LINES].[id]) AS [count], [TBL_TENDER_MEDIAS].[name] AS [category], ROUND(SUM([TBL_SALES_LINES].[amount]-[TBL_SALES_LINES].[change]),0) AS [total]
                FROM [TBL_SALES_LINES], [TBL_SALES], [TBL_TENDER_MEDIAS]
                WHERE [TBL_SALES_LINES].[sales_id]=[TBL_SALES].[id] AND [TBL_SALES_LINES].[tenderMedia_id]=[TBL_TENDER_MEDIAS].[id]
                AND [closed] <= '$tomorrow 07:00' AND [closed] >= '$today 16:00'
                GROUP BY [TBL_TENDER_MEDIAS].[name]";
        $query = $this->db_sf->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_sf->close();        
        
        return $result;
    }
    
    public function get_sf_department($time)
    {
        $this->load_sf_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT SUM([TBL_SALES_LINES].[quantity]) AS [count], [TBL_DEPARTMENTS].[name] AS [category], SUM([TBL_SALES_LINES].[quantity]*[TBL_SALES_LINES].[unitPrice]) AS [total]
                FROM [TBL_SALES_LINES], [TBL_SALES], [TBL_MENUS], [TBL_DEPARTMENTS]
                WHERE [TBL_SALES_LINES].[sales_id]=[TBL_SALES].[id] AND [TBL_SALES_LINES].[menu_id]=[TBL_MENUS].[id] AND [TBL_MENUS].[department_id]=[TBL_DEPARTMENTS].[id]
                AND [closed] <= '$tomorrow 07:00' AND [closed] >= '$today 16:00'
                GROUP BY [TBL_DEPARTMENTS].[name]";
        $query = $this->db_sf->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_sf->close();
        
        return $result;
    }
    
    public function get_velpa_revenue($time)
    {
        $this->load_velpa_db();
        
        $strtime = date('Y-m-d',$time);
        $sql = "SELECT ROUND(SUM([subtotal]),0) AS subtotal, ROUND(SUM([discount]),0) AS discount, ROUND(SUM([serviceAmount]),0) AS \"service\", 
                ROUND(SUM([taxAmount]),0) AS tax, ROUND(SUM([roundingAmount]),0) AS rounding FROM [TBL_SALES] 
                WHERE [date] <= '$strtime 23:59:59' AND [date] >= '$strtime 00:00:00'";
        $query = $this->db_velpa->query($sql);
        $result = $query->row_array();
        
        // Get officer check discount
        $sql = "SELECT SUM(AMOUNT) AS amount from [TBL_SALES_LINES], [TBL_SALES] where [TBL_SALES].[id]=[TBL_SALES_LINES].[sales_id] AND [discount] > 0
                AND [TBL_SALES_LINES].[description] = 'OFFICER CHECK'
                AND [date] <= '$strtime 23:59:59' AND [date] >= '$strtime 00:00:00'";
        $query = $this->db_velpa->query($sql);
        $rslt = $query->row_array();
        $oc = (is_null($rslt['amount']))? 0 : $rslt['amount'];
        $result['discount_customer'] = $result['discount'] - $oc;
        $result['discount_ocheck'] = $oc;
        
        $query->free_result();
        $this->db_velpa->close();
        
        return $result;
    }
    
    public function get_velpa_media_collection($time)
    {
        $this->load_velpa_db();
        
        $strtime = date('Y-m-d',$time);
        $sql = "SELECT COUNT([TBL_SALES_LINES].[id]) AS [count], [TBL_TENDER_MEDIAS].[name] AS [category], ROUND(SUM([TBL_SALES_LINES].[amount]-[TBL_SALES_LINES].[change]),0) AS [total]
                FROM [TBL_SALES_LINES], [TBL_SALES], [TBL_TENDER_MEDIAS]
                WHERE [TBL_SALES_LINES].[sales_id]=[TBL_SALES].[id] AND [TBL_SALES_LINES].[tenderMedia_id]=[TBL_TENDER_MEDIAS].[id]
                AND [date] <= '$strtime 23:59:59' AND [date] >= '$strtime 00:00:00'
                GROUP BY [TBL_TENDER_MEDIAS].[name]";
        $query = $this->db_velpa->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_velpa->close();
        
        return $result;
    }
    
    public function get_velpa_department($time)
    {
        $this->load_velpa_db();
        
        $strtime = date('Y-m-d',$time);
        $sql = "SELECT SUM([TBL_SALES_LINES].[quantity]) AS [count], [TBL_DEPARTMENTS].[name] AS [category], SUM([TBL_SALES_LINES].[quantity]*[TBL_SALES_LINES].[unitPrice]) AS [total]
                FROM [TBL_SALES_LINES], [TBL_SALES], [TBL_MENUS], [TBL_DEPARTMENTS]
                WHERE [TBL_SALES_LINES].[sales_id]=[TBL_SALES].[id] AND [TBL_SALES_LINES].[menu_id]=[TBL_MENUS].[id] AND [TBL_MENUS].[department_id]=[TBL_DEPARTMENTS].[id]
                AND [date] <= '$strtime 23:59:59' AND [date] >= '$strtime 00:00:00'
                GROUP BY [TBL_DEPARTMENTS].[name]";
        $query = $this->db_velpa->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_velpa->close();
        
        return $result;
    }
    
    public function insert_velpa_sales($data)
    {
        $this->load->database();
        $this->db->insert('velpa_sales', $data);
        $this->db->close();
    }
    
    public function get_velpa_sales($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('velpa_sales',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_velpa_sales($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('velpa_sales', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function insert_velpa_payment($data)
    {
        $this->load->database();
        $this->db->insert('velpa_payment', $data);
        $this->db->close();
    }
    
    public function get_velpa_payment($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('velpa_payment',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_velpa_payment($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('velpa_payment', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function insert_cox_sales($data)
    {
        $this->load->database();
        $this->db->insert('cox_sales', $data);
        $this->db->close();
    }
    
    public function get_cox_sales($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('cox_sales',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_cox_sales($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('cox_sales', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function insert_cox_payment($data)
    {
        $this->load->database();
        $this->db->insert('cox_payment', $data);
        $this->db->close();
    }
    
    public function get_cox_payment($time)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->select('*');
        $query = $this->db->get_where('cox_payment',array('date'=>$strtime));
        $result = $query->row_array();
        $this->db->close();
        
        return $result;
    }
    
    public function update_cox_payment($time, $data)
    {
        $this->load->database();
        $strtime = date('Y-m-d',$time);
        $this->db->update('cox_payment', $data, array('date'=>$strtime));
        $this->db->close();
    }
    
    public function get_cox_revenue($time)
    {
        $this->load_cox_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT SUM(subtotal) AS `subtotal`, ROUND(SUM(discountAmount),0) AS `discount`, ROUND(SUM(serviceChargeAmount),0) AS `service`, 
                ROUND(SUM(tax1Amount),0) AS `tax`, ROUND(SUM(roundingAmount),0) AS `rounding` FROM tbl_sales 
                WHERE `created` >= '$today 14:00' AND `created` <= '$tomorrow 07:00'";
        $query = $this->db_cox->query($sql);
        $result = $query->row_array();
        
        // Get officer check discount
        $sql = "SELECT SUM(amount) AS amount FROM tbl_sales_lines, tbl_sales WHERE tbl_sales.id=tbl_sales_lines.sales_id
                AND (tbl_sales_lines.description = 'Officer Check' OR tbl_sales_lines.description = 'OC') 
                AND tbl_sales.`created` >= '$today 14:00' AND tbl_sales.`created` <= '$tomorrow 07:00'";
        $query = $this->db_cox->query($sql);
        $rslt = $query->row_array();
        $oc = (is_null($rslt['amount']))? 0 : $rslt['amount'];
        $result['discount_customer'] = $result['discount'];
        $result['discount_ocheck'] = $oc;
        $result['discount'] += $oc;
        
        $query->free_result();
        $this->db_cox->close();
        
        return $result;
    }
    
    public function get_cox_payment_method($time)
    {
        $this->load_cox_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT COUNT(tbl_sales_lines.id) AS `count`, tbl_payment_methods.name AS `category`, SUM(tbl_sales_lines.amount) AS `total`
                FROM tbl_sales_lines, tbl_sales, tbl_payment_methods
                WHERE tbl_sales_lines.sales_id=tbl_sales.id AND tbl_sales_lines.paymentMethod_id=tbl_payment_methods.id
                AND tbl_sales.`created` >= '$today 14:00' AND tbl_sales.`created` <= '$tomorrow 07:00'
                GROUP BY tbl_payment_methods.name";
        $query = $this->db_cox->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_cox->close();
        
        return $result;
    }
    
    public function get_cox_department($time)
    {
        $this->load_cox_db();
        
        $today = date('Y-m-d',$time);
        $tomorrow = date('Y-m-d',$time+24*60*60);
        $sql = "SELECT SUM(tbl_sales_lines.quantity) AS `count`, tbl_departments.name AS `category`, SUM(tbl_sales_lines.quantity*tbl_sales_lines.unitPrice) AS `total`
                FROM tbl_sales_lines, tbl_sales, tbl_items, tbl_categories, tbl_departments
                WHERE tbl_sales_lines.sales_id=tbl_sales.id AND tbl_sales_lines.item_id=tbl_items.id AND tbl_items.category_id=tbl_categories.id AND tbl_categories.department_id=tbl_departments.id
                AND tbl_sales.`created` >= '$today 14:00' AND tbl_sales.`created` <= '$tomorrow 07:00'
                GROUP BY tbl_departments.name";
        $query = $this->db_cox->query($sql);
        $result = array();
        foreach($query->result_array() as $val)
        {
            $result[] = $val;
        }
        
        $query->free_result();
        $this->db_cox->close();
        
        return $result;
    }
}

?>
